# -*- coding: utf-8 -*-
import numpy as np
import pandas as pd
import pymysql

from MysqlHelp import DB


def get_frequency_fiveMinutes(sizes):
    # 查询在5分钟内,record表中新增的频率信息
    with DB(host='47.92.33.19', user='root', passwd='1qazxsw2', db='database_fm') as db:
        db.execute(
            "SELECT distinct frequency FROM fm_t_scan_record where upload_time BETWEEN date_add(now(), interval - '{}' minute) and now() ".format(
                sizes))
        frequencylist = db.fetchall()
        return frequencylist


def get_deviceid(frequency):
    conn = pymysql.connect(host="47.92.33.19", user="root", password="1qazxsw2", database="database_fm", charset="utf8")
    # 得到一个可以执行SQL语句的光标对象
    cursor = conn.cursor()
    # 定义要执行的SQL语句
    deviceidlists = ''
    args = [frequency, deviceidlists]
    try:

        # 关闭光标对象
        cursor.callproc('proc_to_rssi_location_fm', args)
        sql_result = cursor.fetchall()
        conn.commit()
    except Exception as ex:
        raise ex
    finally:
        cursor.close()
        # 关闭数据库连接
        conn.close()
    deviceidlist = []
    for i in sql_result:
        deviceidlist.append(i[1])

    deviceidset = list(set(deviceidlist))

    deviceid_str = ','.join(deviceidset)
    return deviceid_str


def sel_radiolist(frequency):


    # 查询radiolist表中,该频率是否存在
    conn = pymysql.connect(host="47.92.33.19", user="root", password="1qazxsw2", database="database_fm", charset="utf8")
    # 得到一个可以执行SQL语句的光标对象
    cursor = conn.cursor()
    sql = 'select frequency,fmcoding,lng,lat FROM fm_t_radiolist WHERE frequency = '+frequency+' and ban_time is NULL order by create_time desc'

    try:
        cursor.execute(sql)
        # 关闭光标对象
        sql_result = cursor.fetchall()
    except Exception as ex:
        raise ex
    finally:
        cursor.close()
        # 关闭数据库连接
        conn.close()

    return sql_result

    # print(fre)
    # with DB(host='47.92.93.8',user='root',passwd='1qazxsw2',db='database_fm') as db:
    #
    #     db.execute("select frequency,fmcoding,lng,lat FROM fm_t_radiolist WHERE frequency='{}' and ban_time is NULL order by create_time desc".format(frequency))
    #
    #     codingfrequencydict = db.fetchall()
    #
    #     if len(codingfrequencydict) > 0:
    #         return codingfrequencydict
    #     else:
    #
    #         return None

def ins_fmlocation(nowtime, size, frequency, lng, lat, fmcoding, deviceidList, level, address):
    # with DB(host='47.92.93.8', user='root', passwd='1qazxsw2', db='database_fm') as db:
    with DB(host="47.92.33.19", user="root", passwd="1qazxsw2", db="database_fm") as db:
        db.execute(
            "INSERT into fm_t_fmlocation (id,time,size,frequency,lng,lat,fmcoding,equipmentschedule,positioning_level,address) VALUES(null,'{}','{}','{}','{}','{}','{}','{}','{}','{}')"
            .format(nowtime, size, frequency, lng, lat, fmcoding, deviceidList, level, address))
        print(nowtime)
        print("33333333333333333333333333" + fmcoding)

def get_device_rssi(deviceid,frequency):
    conn = pymysql.connect(host="47.92.33.19", user="root", password="1qazxsw2", database="database_fm", charset="utf8")
    # 得到一个可以执行SQL语句的光标对象
    cursor = conn.cursor()
    sql = '''SELECT deviceid,signal_strength FROM fm_t_scan_record where deviceid = '''+deviceid+''' and frequency = '''+frequency+''' ORDER BY upload_time desc limit 1 '''
    try:

        # 关闭光标对象
        cursor.execute(sql)
        sql_result = cursor.fetchall()
        # resultdict = {}
        # for i in sql_result:
        #     resultdict[i[0]]=i[1]
        return sql_result
    except Exception as ex:
        raise ex
    finally:
        cursor.close()
        # 关闭数据库连接
        conn.close()

def get_device_address(deviceid):
    conn = pymysql.connect(host="47.92.33.19", user="root", password="1qazxsw2", database="database_fm", charset="utf8")
    # 得到一个可以执行SQL语句的光标对象
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    sql = 'SELECT lon,lat FROM fm_t_device where phy_id = '+deviceid+';'
    try:

        # 关闭光标对象
        cursor.execute(sql)
        sql_result = cursor.fetchall()
        # resultdict = {}
        # for i in sql_result:
        #     resultdict[i[0]]=i[1]
        return sql_result[0]
    except Exception as ex:
        raise ex
    finally:
        cursor.close()
        # 关闭数据库连接
        conn.close()

def get_deviceids(frequency):
    conn = pymysql.connect(host="47.92.33.19", user="root", password="1qazxsw2", database="database_fm", charset="utf8")
    # 得到一个可以执行SQL语句的光标对象


    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    deviceidlist = ''
    # 定义要执行的SQL语句
    # sql = '''call proc_to_rssi_location_fm_new('''+frequency+''','''+deviceidlists+''');'''

    args = [frequency, deviceidlist]
    try:

        # 关闭光标对象
        # cursor.callproc('proc_to_rssi_location_fm', args)
        cursor.callproc('proc_to_rssi_location_fm_new', args)
        # cursor.execute(sql )
        # t = cursor.execute('SELECT @_CheckOrderStatus_0,@_CheckOrderStatus_1,@_CheckOrderStatus_2,@_CheckOrderStatus_3;')
        # print(t)
        sql_result = cursor.fetchall()

        conn.commit()
        deviceidlists = []
        devicelist_one = []
        devicelist_two = []
        devicelist_three = []

        # for res in sql_result:
        # print('.......................',sql_result)
        #     print(res)
        for i in sql_result:
            if i['determine'] == 1:
                devicelist_one.append(i['deviceid'])
            elif i['determine'] == 2:
                devicelist_two.append(i['deviceid'])
            else:
                devicelist_three.append(i['deviceid'])

            # numlist.append(i['num'])
            # deviceidDict['deviceid'] = i['deviceid']
            # deviceidDict['num'] = i['num_']
            # devicelist.append(deviceidDict)

        if len(devicelist_one) > 0:
            deviceset_one = list(set(devicelist_one))
            # print(deviceset_one)
            deviceidlists.append(deviceset_one)
        if len(devicelist_two) > 0:
            deviceset_two = list(set(devicelist_two))
            # print(deviceset_two)
            deviceidlists.append(deviceset_two)
        if len(devicelist_three) > 0:
            deviceset_three = list(set(devicelist_three))
            # print(deviceset_three)
            deviceidlists.append(deviceset_three)

        # print(deviceidlists)
        return deviceidlists
        # deviceid_str = ','.join(deviceidset)
        # return deviceid_str
    except Exception as ex:
        raise ex
    finally:
        cursor.close()
        # 关闭数据库连接
        conn.close()


def get_point(deviceid):
    conn = pymysql.connect(host="47.92.33.19", user="root", password="1qazxsw2", database="database_fm", charset="utf8")
    # 得到一个可以执行SQL语句的光标对象
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    sql = 'SELECT lon,lat from fm_t_device WHERE phy_id = '+deviceid+';'
    try:

        # 关闭光标对象
        cursor.execute(sql)
        sql_result = cursor.fetchall()
        # resultdict = {}
        # for i in sql_result:
        #     resultdict[i[0]]=i[1]
        # print('打印sql_result',deviceid,sql_result[0])
        return sql_result[0]
    except Exception as ex:
        raise ex
    finally:
        cursor.close()
        # 关闭数据库连接
        conn.close()